<?php
class SQLQuery {
	const USER_VALIDATION_SQL = "SELECT COUNT(1) FROM USERS WHERE USERNAME LIKE ? AND PASSWORD LIKE ?"; 
	const USER_INFO_SQL ="SELECT * FROM (SELECT USERS.ID AS UID,USERNAME,FIRSTNAME,LASTNAME,EMAIL,ROLE_ID,UNIT_ID,VALID FROM USERS WHERE USERS.USERNAME like ?) AS T1 LEFT JOIN (SELECT ID,ROLE FROM ROLES) AS T2 ON T1.ROLE_ID=T2.ID";
	const USER_OPERATION_SQL ="SELECT distinct(O.OPERATION) AS OPERATION,O.VALIDFOR AS VALIDFOR FROM USERS U, ROLES R,ROLEOPMAPPING RM,OPERATIONS O WHERE U.ROLE_ID = R.ID AND R.ID = RM.ROLE_ID AND RM.OPERATION_ID = O.ID AND U.USERNAME like ?";
	const FIND_COMPANY_UNIT_DETAILS_SQL ="SELECT COMPANY,UNIT FROM COMPANIES,UNITS WHERE UNITS.COMPANY_ID =COMPANIES.ID AND UNITS.ID= ?";
	const COMPANY_ADD = "INSERT INTO COMPANIES (COMPANY_SHORT,COMPANY) VALUES (?,?)";
	const COMPANY_DELETE = "DELETE FROM  COMPANIES WHERE ID = ?";
	const COMPANY_UPDATE = "UPDATE COMPANIES SET COMPANY = ?,COMPANY_SHORT = ? WHERE ID = ?";
	const COMPANY_ADMIN_LIST="SELECT ID,COMPANY,COMPANY_SHORT FROM COMPANIES";
	const COMPANY_USER_LIST="SELECT COMPANIES.ID,COMPANY,COMPANY_SHORT FROM COMPANIES,UNITS WHERE COMPANIES.ID=UNITS.COMPANY_ID AND UNITS.ID=?";
	const COMPANY_FIND="SELECT ID,COMPANY,COMPANY_SHORT FROM COMPANIES WHERE ID=?";
	const COMPANY_FIND_BY_UNIT="SELECT COMPANIES.ID, COMPANY, COMPANY_SHORT FROM COMPANIES,UNITS WHERE UNITS.COMPANY_ID=COMPANIES.ID AND UNITS.ID = ?";
	const UNIT_ADD = "INSERT INTO UNITS (UNIT,UNIT_SHORT,COMPANY_ID) VALUES (?,?,?)";
	const UNIT_DELETE = "DELETE FROM  UNITS WHERE ID = ?";
	const UNIT_UPDATE = "UPDATE UNITS SET UNIT = ?,UNIT_SHORT = ? WHERE ID = ?";
	const UNIT_ADMIN_LIST="SELECT UNITS.ID,UNIT,UNIT_SHORT,COMPANY_ID,COMPANY FROM UNITS LEFT JOIN COMPANIES ON UNITS.COMPANY_ID = COMPANIES.ID";
	const UNIT_USER_LIST="SELECT T1.*,COMPANY FROM (SELECT UNITS.ID,UNIT,UNIT_SHORT,COMPANY_ID FROM UNITS WHERE UNITS.ID =?) AS T1 LEFT JOIN COMPANIES ON T1.COMPANY_ID = COMPANIES.ID ";
	const UNIT_FIND="SELECT T1.*,COMPANY FROM (SELECT UNITS.ID,UNIT,UNIT_SHORT,COMPANY_ID FROM UNITS WHERE UNITS.ID =?) AS T1 LEFT JOIN COMPANIES ON T1.COMPANY_ID = COMPANIES.ID ";
	const FILEDOCUMENT_ADD = "INSERT INTO FILEDETAILS (FILE_NAME,SIZE,UPLOAD_DATE,URL,USERS_ID,UNITS_ID) VALUES (?,?,now(),?,?,?)";
	const FILEDOCUMENT_UPDATE = "UPDATE FILEDETAILS SET FILE_NAME = ? ,SIZE = ?,UPLOAD_DATE = now(),URL = ?,USERS_ID = ?,UINTS_ID = ? WHERE ID = ?";
	const FILEDOCUMENT_DELETE = "DELETE FROM FILEDETAILS WHERE ID = ?";
	const FILEDOCUMENT_LIST_ALL = "SELECT FILEDETAILS.ID,IFNULL(USERS.USERNAME,'') AS USERNAME ,IFNULL(UNITS.UNIT,'') AS UNIT,FILEDETAILS.FILE_NAME,FILEDETAILS.SIZE, FILEDETAILS.UPLOAD_DATE,IFNULL(FILEDETAILS.URL,'#') AS URL FROM FILEDETAILS,USERS,UNITS WHERE FILEDETAILS.USERS_ID=USERS.ID AND FILEDETAILS.UNITS_ID=UNITS.ID ";//To be modified
	const FILEDOCUMENT_LIST = "SELECT FILEDETAILS.ID,IFNULL(USERS.USERNAME,'') AS USERNAME ,IFNULL(UNITS.UNIT,'') AS UNIT,FILEDETAILS.FILE_NAME,FILEDETAILS.SIZE, FILEDETAILS.UPLOAD_DATE,IFNULL(FILEDETAILS.URL,'#') AS URL FROM FILEDETAILS,USERS,UNITS WHERE FILEDETAILS.USERS_ID=USERS.ID AND FILEDETAILS.UNITS_ID=UNITS.ID AND FILEDETAILS.USERS_ID = ?";//To be modified
	const FILEDOCUMENT_FIND = "SELECT FILEDETAILS.ID,IFNULL(USERS.USERNAME,'') AS USERNAME ,IFNULL(UNITS.UNIT,'') AS UNIT,FILEDETAILS.FILE_NAME,FILEDETAILS.SIZE, FILEDETAILS.UPLOAD_DATE,IFNULL(FILEDETAILS.URL,'#') AS URL FROM FILEDETAILS,USERS,UNITS WHERE FILEDETAILS.USERS_ID=USERS.ID AND FILEDETAILS.UNITS_ID=UNITS.ID AND FILEDETAILS.ID = ?";
	const USER_ADD = "INSERT INTO USERS (USERNAME,PASSWORD,FIRSTNAME, LASTNAME, EMAIL,DELETABLE,MODIFIABLE) VALUES (?,md5(?),?,?,?,?,?)";
	const USER_UPDATE = "UPDATE USERS SET USERNAME = ? ,PASSWORD = md5(?), FIRSTNAME = ?, LASTNAME = ?, EMAIL = ?,DELETABLE = ?,MODIFIABLE = ? WHERE ID = ?";
	const USER_DELETE = "DELETE FROM USERS WHERE ID = ?";
	const USER_LIST = "SELECT UID,USERNAME,FIRSTNAME,LASTNAME,EMAIL,IFNULL(COMPANY,'') AS COMPANY,IFNULL(UNIT,'') AS UNIT,MODIFIABLE,DELETABLE,IFNULL(ROLE,'') AS ROLE,CID,UNITID FROM (SELECT ID AS UID,USERNAME,FIRSTNAME,LASTNAME,EMAIL,CID,COMPANY,UNITID,UNIT,MODIFIABLE,DELETABLE,ROLE_ID FROM USERS LEFT JOIN (SELECT COMPANIES.ID AS CID,COMPANIES.COMPANY,UNITS.UNIT,UNITS.ID AS UNITID FROM COMPANIES,UNITS WHERE  COMPANIES.ID = UNITS.COMPANY_ID) CUDESC ON USERS.UNIT_ID =CUDESC.UNITID) AS DESCRIPTION LEFT JOIN (SELECT ROLES.ROLE,ROLES.ID AS RID FROM ROLES) AS ROLE ON DESCRIPTION.ROLE_ID = ROLE.RID ORDER BY USERNAME ASC";
	const USER_FIND = "SELECT UID,USERNAME,FIRSTNAME,LASTNAME,EMAIL,IFNULL(COMPANY,'') AS COMPANY,IFNULL(UNIT,'') AS UNIT,MODIFIABLE,DELETABLE,IFNULL(ROLE,'') AS ROLE FROM (SELECT USRDESC.UID,USERNAME,FIRSTNAME,LASTNAME,EMAIL,COMPANY,UNIT,MODIFIABLE,DELETABLE,ROLE_ID FROM (SELECT ID AS UID,USERNAME,FIRSTNAME,LASTNAME,EMAIL,UNIT_ID,MODIFIABLE,DELETABLE,ROLE_ID FROM USERS WHERE USERS.ID = ?) AS USRDESC LEFT JOIN (SELECT COMPANIES.COMPANY,UNITS.UNIT,UNITS.ID AS UID FROM COMPANIES,UNITS WHERE  COMPANIES.ID = UNITS.COMPANY_ID) AS CUDESC ON USRDESC.UNIT_ID =CUDESC.UID) AS UDESCRIPTION LEFT JOIN (SELECT ROLES.ROLE,ROLES.ID AS RID FROM ROLES) AS ROLE ON UDESCRIPTION.ROLE_ID =ROLE.RID";
	const ROLE_ADD = "INSERT INTO ROLES (ROLE,DESCRIPTION) VALUES (?,?)";
	const ROLE_UPDATE = "UPDATE ROLES SET ROLE = ?, DESCRIPTION =? WHERE ID = ?";
	const ROLE_DELETE = "DELETE FROM  ROLES WHERE ID = ?";
	const ROLE_LIST = "SELECT ID,ROLE,DESCRIPTION FROM ROLES ORDER BY ROLE";
	const ROLE_FIND = "SELECT ID,ROLE,DESCRIPTION FROM ROLES WHERE ID = ?";
	const OPERATION_LIST = "SELECT ID,OPERATION,VALIDFOR,DESCRIPTION FROM OPERATIONS ORDER BY OPERATION";
	const ROLEOPMAPPING_ADD = "INSERT INTO ROLEOPMAPPING (ROLE_ID,OPERATION_ID) VALUES (?,?)";
	const ROLEOPMAPPING_DELETE = "DELETE FROM ROLEOPMAPPING  WHERE ROLE_ID =?";
	const ROLE_OPERATION_FIND = "SELECT O.ID, O.OPERATION,O.VALIDFOR,O.DESCRIPTION,CASE WHEN MAP.OID IS NULL THEN 'false' ELSE 'true' END AS STATUS FROM OPERATIONS O LEFT OUTER JOIN (SELECT RM.OPERATION_ID AS OID FROM ROLES R,ROLEOPMAPPING RM WHERE RM.ROLE_ID = R.ID AND R.ID=?) AS MAP ON MAP.OID= O.ID ";
	const USERROLE_UNSET = "UPDATE USERS SET ROLE_ID=null WHERE ROLE_ID = ?";
	
	const USER_ROLE_LIST_ALL ="SELECT USERS.ID AS UID, USERNAME, ROLES.ID AS RID, ROLE FROM USERS LEFT JOIN ROLES ON USERS.ROLE_ID=ROLES.ID";
	const USER_ROLE_CHECKED = "SELECT ROLES.ID,ROLE,DESCRIPTION, IF(ROLE_ID IS NULL,'UNCHECKED','CHECKED') AS CHECKED FROM ROLES LEFT JOIN (SELECT ROLE_ID FROM USERS WHERE ID = ?) AS T1 ON T1.ROLE_ID = ROLES.ID";
	const USER_ROLE_UPDATE="UPDATE USERS SET ROLE_ID = ? WHERE ID = ?";
	const USER_ROLE_REMOVE="UPDATE USERS SET ROLE_ID = NULL WHERE ID = ?";
	
	const USER_UNIT_LIST_ALL ="SELECT USERS.ID AS UID, USERNAME, T1.UNITID , UNIT,UNIT_SHORT,CID,COMPANY,COMPANY_SHORT FROM USERS LEFT JOIN (SELECT UNITS.ID AS UNITID,UNIT,UNIT_SHORT,COMPANIES.ID AS CID,COMPANY,COMPANY_SHORT FROM UNITS,COMPANIES WHERE UNITS.COMPANY_ID = COMPANIES.ID ) AS T1 ON USERS.UNIT_ID=T1.UNITID ORDER BY USERNAME";
	const USER_UNIT_CHECKED = "SELECT UID,UNIT,UNIT_SHORT, IF(UNIT_ID IS NULL,'UNCHECKED','CHECKED') AS CHECKED FROM UNITS LEFT JOIN (SELECT UNIT_ID FROM USERS WHERE ID = ?) AS T1 ON UNITS.UID = T1.UNIT_ID";
	const USER_UNIT_UPDATE="UPDATE USERS SET UNIT_ID = ? WHERE ID = ?";
	const USER_UNIT_REMOVE="UPDATE USERS SET UNIT_ID = NULL WHERE ID = ?";
	
	
	const STATUTORY_ALL_SQL= "SELECT ID,NAME,DESCRIPTION FROM STATUTORYLAW";
	const STATUTORY_ADD ="INSERT INTO STATUTORYLAW (NAME,DESCRIPTION) VALUES (?,?)";
	const STATUTORY_FIND_BYID ="SELECT NAME,DESCRIPTION FROM STATUTORYLAW WHERE ID = ?";
	const STATUTORY_UPDATE ="UPDATE STATUTORYLAW SET NAME = ? , DESCRIPTION = ? WHERE ID = ?";
	const STATUTORY_DELETE ="DELETE FROM  STATUTORYLAW WHERE ID = ?";
	
	const DOCUMENT_ALL_SQL= "SELECT ID,NAME,DESCRIPTION,PERIOD FROM LAWDOCUMENTS";
	const DOCUMENT_FIND_BYID="SELECT NAME,DESCRIPTION,PERIOD FROM LAWDOCUMENTS WHERE ID = ?";
	const DOCUMENT_ADD ="INSERT INTO LAWDOCUMENTS (NAME,DESCRIPTION,PERIOD) VALUES (?,?,?)";
	const DOCUMENT_UPDATE ="UPDATE LAWDOCUMENTS SET NAME = ? , DESCRIPTION = ? , PERIOD = ? WHERE ID = ?";
	const DOCUMENT_DELETE ="DELETE FROM  LAWDOCUMENTS WHERE ID = ?";
	
	
	const DOCUMENTSTATUTORYMAPPING_FIND_BYID="SELECT A.id AS STATUTORYID, A.NAME AS STATUTORYNAME, B.id AS DOCUMENTID, B.NAME AS DOCUMENTNAME, C.STATUTORY_ID AS MAPPINGSTATUTORYID, C.DOCUMENT_ID AS MAPPINGDOCUMENTID FROM statutorylaw A, lawdocuments B, statutorydocumentmapping C WHERE A.id = C.STATUTORY_ID AND B.id = C.DOCUMENT_ID AND C.DOCUMENT_ID = ? ";		
	const UNMAPPEDSTATUTORYFORDOCUMENT = "SELECT ID, NAME FROM `statutorylaw` where id NOT IN (Select Statutory_ID from statutorydocumentmapping where Document_ID = ?) ";
	const DOCUMENTSTATUTORYMAPPING_DELETE = "DELETE FROM statutorydocumentmapping where DOCUMENT_ID = ?";
	const DOCUMENTSTATUTORYMAPPING_ADD = "INSERT INTO statutorydocumentmapping (DOCUMENT_ID,Statutory_ID) VALUES (?,?)";
	
}
?>